package com.example.DemoDataeaseExample;

import com.example.DemoDataeaseExample.config.KingbaseConfig;
import com.example.DemoDataeaseExample.entity.DatasourceDTO;
import com.example.DemoDataeaseExample.provider.KingbaseProvider;
import com.example.DemoDataeaseExample.type.Kingbase;
import com.example.DemoDataeaseExample.util.JdbcUtils;
import com.example.DemoDataeaseExample.util.JsonUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class KingbaseDemo {

    public static void main(String[] args) {
        try {
            // 初始化并配置数据源
            initializeDatabase();
            DatasourceDTO datasource = createDatasource();

            // 创建提供者并测试连接
            KingbaseProvider provider = new KingbaseProvider();

            try {
                String status = provider.checkStatus(datasource);
                System.out.println("连接状态: " + status);

                if ("Success".equals(status)) {
                    // 仅执行综合测试，包含了基本查询案例
                    executeComprehensiveTests(provider, datasource);
                }
            } catch (Exception e) {
                System.err.println("操作失败: " + e.getMessage());
                e.printStackTrace();
            } finally {
                provider.close();
            }
        } catch (Exception e) {
            System.err.println("程序异常: " + e.getMessage());
            e.printStackTrace();
        }
    }

    // 创建数据源配置
    private static DatasourceDTO createDatasource() {
        Kingbase configuration = new Kingbase();
        configuration.setHost(KingbaseConfig.DEFAULT_HOST);
        configuration.setPort(Integer.parseInt(KingbaseConfig.DEFAULT_PORT));
        configuration.setDataBase(KingbaseConfig.DEFAULT_DATABASE);
        configuration.setUsername(KingbaseConfig.DEFAULT_USERNAME);
        configuration.setPassword(KingbaseConfig.DEFAULT_PASSWORD);

        String configJson = JsonUtil.toJSONString(configuration);

        DatasourceDTO datasource = new DatasourceDTO();
        datasource.setType("kingbase");
        datasource.setName("人大金仓测试");
        datasource.setConfiguration(configJson);
        datasource.setJdbcUrl(KingbaseConfig.getDefaultJdbcUrl());
        datasource.setUsername(KingbaseConfig.DEFAULT_USERNAME);
        datasource.setPassword(KingbaseConfig.DEFAULT_PASSWORD);

        return datasource;
    }

    // 初始化数据库
    private static void initializeDatabase() {
        Connection conn = null;
        Statement stmt = null;

        try {
            System.out.println("初始化数据库...");
            Class.forName(KingbaseConfig.KINGBASE_DRIVER);
            conn = DriverManager.getConnection(
                    KingbaseConfig.getDefaultJdbcUrl(),
                    KingbaseConfig.DEFAULT_USERNAME,
                    KingbaseConfig.DEFAULT_PASSWORD
            );

            stmt = conn.createStatement();

            // 创建测试表和数据
            createTestTables(stmt);
            insertTestData(stmt);

            System.out.println("数据初始化成功!");
        } catch (Exception e) {
            System.err.println("初始化数据库失败: " + e.getMessage());
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn, stmt, null);
        }
    }

    private static void createTestTables(Statement stmt) throws SQLException {
        // 创建用户表
        try { stmt.executeUpdate("DROP TABLE IF EXISTS test_user"); } catch (SQLException e) {}
        stmt.executeUpdate("CREATE TABLE test_user (" +
                "id INTEGER PRIMARY KEY, " +
                "name VARCHAR(100), " +
                "age INTEGER, " +
                "dept_id INTEGER, " +
                "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
                ")");

        // 创建部门表
        try { stmt.executeUpdate("DROP TABLE IF EXISTS department"); } catch (SQLException e) {}
        stmt.executeUpdate("CREATE TABLE department (" +
                "dept_id INTEGER PRIMARY KEY, " +
                "dept_name VARCHAR(100)" +
                ")");
    }

    private static void insertTestData(Statement stmt) throws SQLException {
        // 插入部门数据
        stmt.executeUpdate("INSERT INTO department(dept_id, dept_name) VALUES(1, '研发部')");
        stmt.executeUpdate("INSERT INTO department(dept_id, dept_name) VALUES(2, '市场部')");

        // 插入用户数据
        stmt.executeUpdate("INSERT INTO test_user(id, name, age, dept_id) VALUES(1, '张三', 25, 1)");
        stmt.executeUpdate("INSERT INTO test_user(id, name, age, dept_id) VALUES(2, '李四', 30, 2)");
    }

    // 综合测试方法
    private static void executeComprehensiveTests(KingbaseProvider provider, DatasourceDTO datasource) {
        Connection conn = null;
        Statement stmt = null;

        try {
            // 获取Calcite连接
            conn = provider.getCalciteConnection(datasource);
            
            System.out.println("\n===== comprehensive Query =====");
            // 执行基本查询测试
            System.out.println("\n===== 基本查询测试 =====");
            
            // 为每个查询创建新的Statement以避免潜在问题
            stmt = provider.getStatement(conn, datasource.getQueryTimeout());
            executeTest(provider, stmt, "SELECT * FROM KINGBASE.test_user ORDER BY id", "测试1: 基本查询");
            
            stmt = provider.getStatement(conn, datasource.getQueryTimeout());
            executeTest(provider, stmt, "SELECT * FROM KINGBASE.test_user WHERE id > 0", "测试2: 带WHERE条件的查询");
            
            stmt = provider.getStatement(conn, datasource.getQueryTimeout());
            executeTest(provider, stmt, "SELECT * FROM KINGBASE.test_user WHERE name = '张三'", "测试3: 中文字符条件查询");
            
            stmt = provider.getStatement(conn, datasource.getQueryTimeout());
            executeTest(provider, stmt, "SELECT * FROM KINGBASE.test_user WHERE name = '张三' OR name = '李四'", "测试4: 多个中文条件");
            

            // 执行复杂查询测试
            System.out.println("\n===== 复杂查询测试 =====");
            
            stmt = provider.getStatement(conn, datasource.getQueryTimeout());
            executeTest(provider, stmt,
                    "SELECT u.id, u.name, d.dept_name FROM KINGBASE.test_user u JOIN KINGBASE.department d ON u.dept_id = d.dept_id",
                    "测试7: 联表查询");
            
            stmt = provider.getStatement(conn, datasource.getQueryTimeout());
            executeTest(provider, stmt,
                    "SELECT * FROM KINGBASE.test_user WHERE dept_id IN (SELECT dept_id FROM KINGBASE.department WHERE dept_name = '研发部')",
                    "测试8: 嵌套查询");
            
            stmt = provider.getStatement(conn, datasource.getQueryTimeout());
            executeTest(provider, stmt,
                    "SELECT id, name, CASE WHEN age > 25 THEN '高龄' ELSE '年轻' END as age_level FROM KINGBASE.test_user",
                    "测试11: CASE语句");

            // 执行特殊语法测试
            System.out.println("\n===== 特殊语法测试 =====");
            
            stmt = provider.getStatement(conn, datasource.getQueryTimeout());
            executeTest(provider, stmt,
                    "SELECT * FROM KINGBASE.test_user /* 这是一个注释 */ WHERE id > 0",
                    "测试15: 带注释的SQL");

        } catch (Exception e) {
            System.err.println("综合测试执行失败: " + e.getMessage());
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn, stmt, null);
        }
    }

    // 辅助方法，用于执行单个测试
    private static void executeTest(KingbaseProvider provider, Statement stmt, String sql, String testName) {
        ResultSet rs = null;
        try {
            System.out.println("\n--- " + testName + " ---");

            // SQL转换
            String convertedSql = provider.convertSql(sql);

            // 执行查询
            rs = stmt.executeQuery(convertedSql);

            // 转换结果集
            List<Map<String, Object>> dataset = resultSetToDataset(rs);
            System.out.println("结果集大小: " + dataset.size());
            System.out.println("数据集结果: " + JsonUtil.toJSONString(dataset));

            System.out.println("状态: 成功");
            
            // 添加短暂延时，让连接池有时间恢复
            try {
                Thread.sleep(100);
            } catch (InterruptedException ie) {
                Thread.currentThread().interrupt();
            }
        } catch (Exception e) {
            System.err.println("状态: 失败");
            System.err.println("错误: " + e.getMessage());
            e.printStackTrace();
        } finally {
            // 确保ResultSet被关闭
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    System.err.println("关闭ResultSet失败: " + e.getMessage());
                }
            }
        }
    }

    // 将ResultSet转换为通用数据集格式
    private static List<Map<String, Object>> resultSetToDataset(ResultSet rs) throws SQLException {
        List<Map<String, Object>> dataset = new ArrayList<>();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        while (rs.next()) {
            Map<String, Object> row = new HashMap<>();

            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                Object value = rs.getObject(i);
                row.put(columnName, value);
            }

            dataset.add(row);
        }

        return dataset;
    }
}